package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 * DBProduct.java
 * @author 
 * @version 
 */
public class DBProduct {
    private  Connection con;
 
    /** Creates a new instance of DBEmployee */
    public DBProduct() {
    	con = DbConnection1.getInstance().getDBcon();
    }
    
  //Implements the methods from the interface
    // get all products
    public ArrayList<Product> getAllProducts(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    //get one product having the productID
    public  Product findProduct(int productId, boolean retriveAssociation)
    {   String wClause = "  productId = '" + productId + "'";
        return singleWhere(wClause, retriveAssociation);
    }
    //find one product having the pName
    public Product searchProductPName(String attValue, boolean retriveAssociation)
    {
        String wClause = "pName like '%" + attValue + "%'";
        System.out.println("SearchProduct " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
     
     //insert a new product
    public int insertProduct(Product prod) throws Exception
    {  //call to get the next productId number
        int nextProductId = GetMax.getMaxId("Select max(productId) from product");
        nextProductId = nextProductId + 1;
        System.out.println("next productId = " +  nextProductId);
  
       int rc = -1;
	   String query="INSERT INTO product(productId, pName, puchasePrice, salesPrice, " +
	   		"countryOfOrigin, minstock, stock, typeOfProduct, supId)  VALUES('"+
	   		nextProductId + "','" +		
	   		prod.getPName() + "','"  +
	   		prod.getPurchasePrice() + "','"  +
	        prod.getSalesPrice() + "','"  +
	        prod.getCountryOfOrigin() + "','"  +
	        prod.getMinStock() + "','"  +
            prod.getStock() + "','"  +
	        prod.getTypeOfProduct() + "','"  +
	        prod.getSupplier() + "')";

       System.out.println("insert : " + query);
      try{ // insert new product 
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Product ikke oprettet");
          throw new Exception ("Product is not inserted correct");
       }
       return(rc);
     }

    public int updateProduct(Product product)
	{
    	Product myProduct = product;
		int rc=-1;

		String query="UPDATE product SET "+
		 	  "salesPrice ='"+ myProduct.getSalesPrice()+"', "+
		 	  "stock ='"+ myProduct.getStock() + "', " +
		 	  "typeOfProduct ='"+ myProduct.getTypeOfProduct() + "', " +
		          " WHERE productID = '"+ myProduct.getProductId() + "'";
                System.out.println("Update query:" + query);
  		try{ // update product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
		return(rc);
	}
	
	public int delete(int productID)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM product WHERE productID = '" +
	  			productID + "'";
                System.out.println(query);
	  	try{ // delete from product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//end try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in product db: "+ex);
   	        }
		return(rc);
	}
	
	//private methods
    //michWere is used whenever we want to select more than one product
	 
	private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Product> list = new ArrayList<Product>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the product from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
			Product pObj = new Product();
			pObj = buildProduct(results);	
                 list.add(pObj);	
		}//end while
                 stmt.close();     
                 if(retrieveAssociation)
                 {   //The supplier is to be build as well
                     for(Product pObj : list){
                        DBSupplier dbSupObj = new DBSupplier();
                        Supplier supObj = dbSupObj.findSupplier(pObj.getSupplier().getSuppId(), false);
                        System.out.println("Supplier is seleceted ");
                        pObj.setSupplier(supObj);   
                     }
                 }//end if   
			
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	

	//singlewhere is used when we only select one product 	
	private Product singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Product pObj = new Product();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the product from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
	 			System.out.println("hello");
	 			
                            pObj = buildProduct(results);
                            System.out.println("hello");
                            //Association is to be build
                            stmt.close();
                            if(retrieveAssociation)
                            {   //The supplier is to be build as well
                                
                                    DBSupplier dbSupObj = new DBSupplier();
                                    Supplier supObj = dbSupObj.findSupplier(pObj.getSupplier().getSuppId(), false);
                                    System.out.println("Supplier is seleceted ");
                                    pObj.setSupplier(supObj);                                  
                            }
			
       //          
	 		}
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return pObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Product";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build an product object
	private Product buildProduct(ResultSet results)
      {   Product pObj = new Product();
          try{ // the columns from the table Product  are used
        	  System.out.println("hello");
                pObj.setProductId(results.getInt("productId"));
                pObj.setPName(results.getString("pName"));
                pObj.setPurchasePrice(results.getInt("purchasePrice"));
                System.out.println("hello");
                pObj.setSalesPrice(results.getInt("salesPrice"));
                pObj.setCountryOfOrigin(results.getString("countryOfOrigin"));
                pObj.setMinStock(results.getInt("minstock"));
                System.out.println("hello");
                pObj.setStock(results.getInt("stock"));
                pObj.setTypeOfProduct(results.getString("typeOfProduct"));
                pObj.setSupplier(new Supplier(results.getInt("supID")));
          }
         catch(Exception e)
         {
             System.out.println("error in building the product object");
         }
          System.out.println("hello" + pObj);
         return pObj;
      }
}  
    

